import pandas as pd
import numpy
import xlwt


def anaysis(path1, path2, list1, list2,wb,ws):
    '''
    :param path1: 成绩分析表
    :param path2: 输出路径
    :param list1: 分数 列表
    :param list2: 课程质保数值 列表
    :param sheel: 表名，excel左下角名称
    :return:
    '''
    df = pd.read_excel(path1, header=5)
    df = df.iloc[:, :13]
    df.columns = ['学号', '姓名', '课程编号', '考试科目', '考试状态', '性别', '是否留学生', '成绩', '学院', '考试日期', '班级', '年纪', '备注']
    row = len(list1)
    col = len(list1[0])
    a = numpy.zeros((row, col))
    df1 = pd.DataFrame(a)
    for x in range(row):
        for y in range(col):
            df1.loc[x, y] = list1[x][y]
    df1_columns = ['score' + str(i) for i in range(1, col + 1)]
    df1.columns = df1_columns
    df1['all_score'] = 0
    for x in range(1, col + 1):
        df1['all_score'] += df1['score' + str(x)]
    df1_columns.append('all_score')
    row_2 = len(list2)
    col_2 = len(list2[1])
    b = numpy.zeros((row_2, col_2))
    df2 = pd.DataFrame(b)
    for x in range(row_2):
        for y in range(col_2):
            df2.loc[x, y] = list2[x][y]
    df2_columns = ['课程目标' + str(i) for i in range(1, 5)]
    df2.columns = df2_columns
    df_finally = pd.concat([df, df1, df2], axis=1)
    columns = ['学号', '姓名', '课程编号', '考试科目', '考试状态', '性别', '是否留学生', '成绩', '学院', '考试日期', '班级', '年纪',
               '备注'] + df1_columns + df2_columns
    df_finally = df_finally.loc[:, columns]
    for i in range(0, df.shape[0]):
        data_list = df_finally.iloc[i, :].tolist()
        for j in range(0, len(data_list)):
            if type(data_list[j]) == numpy.int64:
                ws.write(i + 6, j , int(data_list[j]))
            else:
                ws.write(i + 6, j, data_list[j])
    wb.save(path2)



